<?php
/**
 * Created by PhpStorm.
 * User: ZSYK
 * Date: 2015/12/31
 * Time: 8:35
 */

namespace Icsoc\EsReportBundle\Model;

use Elasticsearch\Client;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Class ReportModel
 * @package Icsoc\ReportBundle\Model
 */
class ReportModel extends BaseModel
{
    /** @var \Doctrine\DBAL\Connection  */
    protected $container;
    private $conn;
    private $logger;

    /** @var array */
    private $fixedTitle = array(
        'system' => array(
            'date'=>array('text'=>'Date', 'field'=>'date', 'sortable'=>true, 'width'=>110, 'default_show'=> true),
        ),
        'queue' => array(
            'queue_name' => array(
                'text'=>'Queue Name',
                'field'=>'queue_name',
                'sortable'=>true,
                'width'=>120,
                'default_show'=> true,
            ),
            'date' => array('text'=>'Date', 'field'=>'date', 'sortable'=>true, 'width'=>110, 'default_show'=> true),
        ),
        'agent' => array(
            'agent' => array('text'=>'Agent', 'field'=>'agent', 'sortable'=>true, 'width'=>100, 'default_show'=> true),
            'date' => array('text'=>'Date', 'field'=>'date', 'sortable'=>true, 'width'=>110, 'default_show'=> true),
        ),
        'group' => array(
            'group_name' => array(
                'text'=>'Group Name',
                'field'=>'group_name',
                'sortable'=>true,
                'width'=>100,
                'default_show'=> true,
            ),
            'date' => array('text'=>'Date', 'field'=>'date', 'sortable'=>true, 'width'=>110, 'default_show'=> true),
        ),
    );

    /**
     * 四中类型对应的ES中的Type
     * @var array
     */
    private $types = array(
        'month' => 'rep_%s_month',
        'day' => 'rep_%s_day',
        'hour' => 'rep_%s_hour',
        'halfhour' => 'rep_%s_halfhour',
    );

    /**
     * @param ContainerInterface $container
     */
    public function __construct(ContainerInterface $container)
    {
        $this->container = $container;
        $this->conn = $this->container->get('doctrine.dbal.default_connection');
        $this->logger = $this->container->get('logger');
    }

    /**
     * 获取数据
     * @param array $param
     * @return array|int
     */
    public function getData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $param['info'];
        $repType = empty($param['rep_type']) ? 'month' : $param['rep_type'];
        $type = empty($param['type']) ? 'system' : $param['type'];
        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/"; //2015-05 格式判断
        /** @var array $msg  验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message'=>'info格式非json');
            }
        }

        if (!isset($this->types[$repType])) {
            return array('code'=>404, 'message'=>'type格式不正确');
        }
        $esType = sprintf($this->types[$repType], $type);
        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        $field = empty($info['sort']['field']) ? 'current_date' : $info['sort']['field'];
        $condition = array(
            'fixup' => array(
                'index' => $this->container->getParameter('elasticsearch_index_name'),
                'type' => $esType,
                'rows' => $rows == - 1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => $field,
            ),
            'term' => array('vcc_id'=>array('type'=>'match', 'value'=>$vccId)),
        );

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_date']) && !empty($info['filter']['start_date'])) {
                $startDate = $info['filter']['start_date'];
                switch ($repType) {
                    case 'month':
                        $msg = $this->container->get('icsoc_data.helper')->regexRormat($reg, $startDate, '开始日期不正确', 404);
                        $startDate.="-01";
                        break;

                    case 'day':
                    case 'hour':
                    case 'halfhour':
                        $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $startDate, 404);
                        break;
                }

                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }

                $condition['term']['"start_date'] = array('type'=>'range', 'value'=>$startDate, 'field'=>'current_date', 'operation'=>'gte');
            }
            //结束时间
            if (isset($info['filter']['end_date']) && !empty($info['filter']['end_date'])) {
                $endDate = $info['filter']['end_date'];
                switch ($repType) {
                    case 'month':
                        $msg = $this->container->get('icsoc_data.helper')->regexRormat($reg, $endDate, '开始日期不正确', 405);
                        $endDate = date("Y-m-t", strtotime($endDate."-01"));
                        break;

                    case 'day':
                    case 'hour':
                    case 'halfhour':
                        $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $endDate, 405);
                        break;
                }
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }

                $condition['term']['end_date'] = array('type'=>'range', 'value'=>$endDate, 'field'=>'current_date', 'operation'=>'lte');
            }

            //搜索的字段(agent,queue,group)
            if (isset($info['filter']['data_id']) && !empty($info['filter']['data_id'])) {
                $condition = $this->setCondition($condition, $type, $info['filter']['data_id']);
            }
        }
        $condition['export'] = empty($info['export']) ? 0 : 1;
        $rows = $this->searchData($condition, $repType, $type);

        return $rows;
    }

    /**
     * @param array $param
     * @return array
     */
    public function getTitleReport($param)
    {
        $title = array();
        $reportName = isset($param['report_name']) ? $param['report_name'] : '';
        $reportType = isset($param['report_type']) ? $param['report_type'] : '';
        if (empty($reportName)) {
            return array('code'=>401, 'message'=>'报表名称不能为空');
        }
        if (($reportName == 'agent' || $reportName == 'group') && empty($reportType)) {
            return array('code'=>402, 'message'=>'报表类型不能为空');
        }
        $param['data_type'] = 'title';
        $rst = $this->processConfig($param);
        if (isset($rst['code']) && $rst['code'] == 200) {
            $title = $rst['data'];
        }
        foreach ($title as $key => $val) {
            if ($val['default_show'] == false) {
                unset($title[$key]);
            }
        }
        $title = array_merge($this->fixedTitle[$reportName], $title);

        return array('code'=>200, 'message'=>'success', 'data'=>$title);
    }

    /**
     * @param array $param
     * @return array
     */
    private function getDataReport($param)
    {
        $title = array();
        $count = array();
        $data = isset($param['data']) ? $param['data'] : array();
        $reportName = isset($param['report_name']) ? $param['report_name'] : '';
        $workTime = isset($param['work_time']) ? $param['work_time'] : '';
        $repType = isset($param['rep_type']) ? $param['rep_type'] : '';
        if (empty($data)) {
            return array('code'=>401, 'message'=>'数据不能为空');
        }
        if (empty($reportName)) {
            return array('code'=>402, 'message'=>'报表名称不能为空');
        }
        if ($reportName == 'system' && empty($workTime)) {
            return array('code'=>403, 'message'=>'workTime不能为空');
        }
        $param['data_type'] = 'data';
        $rst = $this->processConfig($param);

        if (isset($rst['code']) && $rst['code'] == 200) {
            $title = $rst['data']['title'];
            $count = $rst['data']['count'];
        }

        $gridData = array();
        $footer = array();//统计用
        foreach ($data as $key => $value) {
            $value = $this->proccessValue($value, $reportName);
            //处理footer
            foreach ($value as $k => $v) {
                if (!in_array($k, array('agent', 'queue_name', 'group_name'))) {
                    $footer[$k] = isset($footer[$k]) ? $footer[$k] + $v : $v;
                }
            }
            $value = $this->addCalculationField($value, $count, $title, $reportName);//添加计算字段
            $value['date'] = $this->processDate($value, $repType);//处理日期字段；
            $gridData[$key] = $value;
        }
        $footer = $this->addCalculationField($footer, $count, $title, $reportName);//处理footer的计算字段；
        $footer['date'] = $this->container->get("translator")->trans("Total"); //把footer的日期变成合计；

        return array('code'=>200, 'message'=>'success', 'data'=>$gridData, 'footer'=>$footer);
    }

    /**
     * 处理配置信息
     * @param array $param
     * @return array
     */
    private function processConfig($param)
    {
        $reportName = isset($param['report_name']) ? $param['report_name'] : '';
        $reportType = isset($param['report_type']) ? $param['report_type'] : '';
        $dataType = isset($param['data_type']) ? $param['data_type'] : '';
        $defaultTitle = $this->reportItems[$reportName]['fixed_report'];
        $defaultCount = $this->calculateItems[$reportName];
        $x = array(10, 20, 30, 40); //x秒;
        $default = array();
        switch ($reportName) {
            case 'system':
                $default = $this->proccessXSecs($defaultTitle, $defaultCount, $x);//X秒接通率
                break;
            case 'queue':
                $default = $this->proccessXSecs($defaultTitle, $defaultCount, $x, $x);//X秒接通率以及X秒放弃率；
                break;
            case 'agent':
            case 'group':
                $defaultTitle = $this->proccessStaReason($defaultTitle, $reportType); //置忙原因指标统计；
                break;
        }
        if (!empty($default)) {
            $defaultTitle = $default['title'];
            $defaultCount = $default['count'];
        }

        $title = $defaultTitle;
        $count = $defaultCount;
        $user = $this->container->get('security.token_storage')->getToken()->getUser();
        $loginType = $user->getLoginType();

        //如果是坐席(win_agent)登陆，处理报表配置
        if ($loginType == 2) {
            $userRole =  $user->getUserRole();
            try {
                $reportConfig = $this->conn->fetchColumn("SELECT report_config FROM cc_roles WHERE role_id=$userRole");
            } catch (\Exception $e) {
                $this->logger->error($e->getMessage());

                return array('code'=>400, 'message'=>'数据库执行错误');
            }
            $reportConfig = json_decode($reportConfig, true);
            $fields = isset($reportConfig[$reportName]['fixed']) ? $reportConfig[$reportName]['fixed'] : array();
            $calculate = isset($reportConfig[$reportName]['calculateItems']) ?
                $reportConfig[$reportName]['calculateItems'] : array();
            if (!empty($fields)) {
                foreach ($title as $k => $v) {
                    $title[$k]['default_show'] = in_array($k, $fields) ? true : false;
                }
            }
            if (!empty($calculate)) {
                $count = array_merge($defaultCount, $calculate);
            }
        }

        if ($dataType == 'data') {
            $title = array('title'=>$defaultTitle, 'count'=>$count);
        }

        return array('code' => 200, 'message' => 'success', 'data' => $title);
    }

    /**
     * 给vaue添加计算的字段
     * @param $value
     * @param $count
     * @param $title
     * @param $reportName
     * @return mixed
     */
    private function addCalculationField($value, $count, $title, $reportName)
    {
        foreach ($count as $k => $v) {
            $divisorPlusSum = 0;
            $divisorMinusSum = 0;
            $dividendPlusSum = 0;
            $dividendMinusSum = 0;

            if (!isset($title[$k]['field'])) {
                continue;
            }

            if (isset($v['numerator']['plus'])) {
                foreach ($v['numerator']['plus'] as $val) {
                    if (!isset($title[$val]['field'])) {
                        continue;
                    }
                    $divisorPlusSum += isset($value[$title[$val]['field']]) ? $value[$title[$val]['field']] : 0;
                }
            }

            if (isset($v['numerator']['minus'])) {
                foreach ($v['numerator']['minus'] as $val) {
                    if (!isset($title[$val]['field'])) {
                        continue;
                    }
                    $divisorMinusSum += isset($value[$title[$val]['field']]) ? $value[$title[$val]['field']] : 0;
                }
            }
            $numerator = $divisorPlusSum - $divisorMinusSum;

            if (!isset($v['denominator'])) {
                if ($reportName == 'agent' || $reportName == 'group') {
                    $value[$title[$k]['field']] = $numerator;
                    continue;
                }
                $value[$title[$k]['field']] = $numerator > 0 ? $numerator : 0 ;
                continue;
            }

            if (isset($v['denominator']['plus'])) {
                foreach ($v['denominator']['plus'] as $val) {
                    if (!isset($title[$val]['field'])) {
                        continue;
                    }
                    $dividendPlusSum += isset($value[$title[$val]['field']]) ? $value[$title[$val]['field']] : 0;
                }
            }
            if (isset($v['denominator']['minus'])) {
                foreach ($v['denominator']['minus'] as $val) {
                    if (!isset($title[$val]['field'])) {
                        continue;
                    }
                    $dividendMinusSum += isset($value[$title[$val]['field']]) ? $value[$title[$val]['field']] : 0;
                }
            }
            $denominator = $dividendPlusSum - $dividendMinusSum;

            if (isset($v['percent']) && $v['percent'] == 1) {
                $value[$title[$k]['field']] = '0%';
                if ($denominator > 0) {
                    $value[$title[$k]['field']] = round($numerator / $denominator * 100, 2);
                    $value[$title[$k]['field']] = $value[$title[$k]['field']] > 100 ?
                        '100%' : $value[$title[$k]['field']].'%';
                }
            } else {
                $value[$title[$k]['field']] = $denominator > 0 ? round($numerator / $denominator) : $numerator;
            }
        }

        return $value;
    }

    /**
     * 处理时间
     * @param $value
     * @param $type
     * @return string
     */
    private function processDate($value, $type)
    {
        switch ($type) {
            default:
            case 'day':
            case 'month':
                return $value['current_time'].'-'.$value['time_stamp'];
                break;
            case 'hour':
                return $value['current_time'].' '.$value['time_stamp'].'时';
                break;
            case 'halfhour':
                $hour = sprintf("%02d", floor($value['time_stamp']/2)).":";
                $minute = $value['time_stamp']%2 == 1 ? "30" : "00";

                return $value['current_time'].' '.$value['time_stamp'].' '.$hour.$minute;
                break;
        }
    }

    /**
     * 处理X秒的字段
     * @param $defaultTitle
     * @param $defaultCount
     * @param array $connect
     * @param array $waive
     * @return mixed
     */
    private function proccessXSecs($defaultTitle, $defaultCount, $connect = array(), $waive = array())
    {
        foreach ($connect as $v) {
            $defaultTitle['inboundConnIn'.$v.'SecsTotalNum'] = array(
                'text'=>sprintf("Callin %s Num", $v.'s'),
                'field'=>'inboundConnIn'.$v.'SecsTotalNum',
                'sortable'=>true,
                'width'=>110,
                'default_show'=> true,
            );
            $defaultTitle['inboundConnIn'.$v.'SecsTotalRate'] = array(
                'text'=>sprintf("Callin %s Rate", $v.'s'),
                'field'=>'inboundConnIn'.$v.'SecsTotalRate',
                'sortable'=>true,
                'width'=>110,
                'default_show'=> true,
            );
            $defaultCount['inboundConnIn'.$v.'SecsTotalRate'] = array(
                'numerator' => array(
                    'plus'=> array('inboundConnIn'.$v.'SecsTotalNum'),
                ),
                'denominator' => array(
                    'plus'=> array('inboundConnNum'),
                ),
                'percent' => 1,
            );
        }

        foreach ($waive as $v) {
            $defaultTitle['inboundAbandonIn'.$v.'SecsNum'] = array(
                'text'=>sprintf("Callin Lost %s Num", $v.'s'),
                'field'=>'inboundAbandonIn'.$v.'SecsNum',
                'sortable'=>true,
                'width'=>110,
                'default_show'=> true,
            );
            $defaultTitle['inboundAbandonIn'.$v.'SecsRate'] = array(
                'text'=>sprintf("Callin Lost %s Rate", $v.'s'),
                'field'=>'inboundAbandonIn'.$v.'SecsRate',
                'sortable'=>true,
                'width'=>110,
                'default_show'=> true,
            );
            $defaultCount['inboundAbandonIn'.$v.'SecsRate'] = array(
                'numerator' => array(
                    'plus'=> array('inboundAbandonIn'.$v.'SecsNum'),
                ),
                'denominator' => array(
                    'plus'=> array('inboundAbandonTotalNum'),
                ),
                'percent' => 1,
            );
        }

        return array('title'=>$defaultTitle, 'count'=>$defaultCount);
    }

    /**
     * 处理置忙原因字段
     * @param $defaultTitle
     * @param $reportType
     * @return mixed
     */
    private function proccessStaReason($defaultTitle, $reportType)
    {
        if ($reportType != 'halfhour') {
            $user = $this->container->get('security.token_storage')->getToken()->getUser();
            $vccId = $user->getVccId();
            $agentStaReason = $this->container->get('icsoc_data.model.report')
                ->getAgentStaReason($vccId);
            foreach ($agentStaReason as $key => $v) {
                $defaultTitle['agstanum'.$key] = array(
                    'text'=>$v.'次数',
                    'field'=>'agstanum'.$key,
                    'sortable'=>false,
                    'width'=>90,
                    'default_show'=> true,
                );
                if ($reportType == 'day' || $reportType == 'month') {
                    $defaultTitle['agstaduration'.$key] = array(
                        'text'=>$v.'时长',
                        'field'=>'agstaduration'.$key,
                        'sortable'=>false,
                        'width'=>90,
                        'default_show'=>true,
                    );
                }
            }
            if (!empty($agentStaReason)) {
                $defaultTitle['agstanum_other'] = array(
                    'text' => '其他置忙次数',
                    'field' => 'agstanum_other',
                    'sortable' => false,
                    'width' => 90,
                    'default_show' => true,
                );
                if ($reportType == 'day' || $reportType == 'month') {
                    $defaultTitle['agstaduration_other'] = array(
                        'text' => '其他置忙时长',
                        'field' => 'agstaduration_other',
                        'sortable' => false,
                        'width' => 90,
                        'default_show' => true,
                    );
                }
            }
        }

        return $defaultTitle;
    }


    /**
     * 公共数据处理
     * @param $data
     * @param $vccId
     * @param $repType
     * @param $type
     * @return array
     */
    private function processData($data, $vccId, $repType, $type)
    {
        /** @var  $configs (工作量小时、天) */
        $configs = $this->container->get("doctrine.dbal.default_connection")->fetchAssoc(
            "SELECT work_day,work_hour,conn_num,lost_num
            FROM cc_ccod_configs
            WHERE vcc_id = $vccId"
        );

        $workDay  = empty($configs['work_day'])  ? 30 : $configs['work_day'];
        $workHour = empty($configs['work_hour']) ? 24 : $configs['work_hour'];
        switch($repType) {
            case 'month':
                $workTime = $workDay*$workHour*3600;
                break;
            case 'day':
                $workTime = $workHour*3600;
                break;
            case 'hour':
                $workTime = 3600;
                break;
            case 'halfhour':
                $workTime = 1800;
                break;
            default:
                $workTime = 3600;
                break;
        }
        $param = array(
            'data' => $data,
            'report_name' => $type,
            'work_time' => $workTime,
            'rep_type' => $repType,
        );
        $gridData = array();
        $footer = array();
        $rst = $this->getDataReport($param);
        if (isset($rst['code']) && $rst['code'] == 200) {
            $gridData = $rst['data'];
            $footer = $rst['footer'];
        }

        return array('data'=>$gridData, 'footer'=>$footer);
    }

    /**
     * 从ES中搜索数据
     * @param array $condition
     * @param $repType
     * @param $type
     * @return array
     */
    private function searchData(array $condition, $repType, $type)
    {
        $index = isset($condition['fixup']['index']) ? $condition['fixup']['index'] : '';
        $esType = isset($condition['fixup']['type']) ? $condition['fixup']['type'] : '';
        $rows = isset($condition['fixup']['rows']) ? $condition['fixup']['rows'] : 10;
        $page = isset($condition['fixup']['page']) ? $condition['fixup']['page'] : 1;
        $field = isset($condition['fixup']['field']) ? $condition['fixup']['field'] : 'start_time';
        $order = isset($condition['fixup']['order']) ? $condition['fixup']['order'] : 'desc';
        $export = isset($condition['export']) ? $condition['export'] : '';
        $from = $rows * $page - $rows;
        $from = $from > 0 ? $from : 0;
        $query = array(
            'query' => array(),
        );
        if (is_array($field)) {
            foreach ($field as $f) {
                $query['sort'][] = array($f=>array('order'=>$order, 'unmapped_type'=>'date'));
            }
        } else {
            $query['sort'] = array($field=>array('order'=>$order, 'unmapped_type'=>'date'));
        }

        $terms = $condition['term'];
        foreach ($terms as $k => $v) {
            switch ($v['type']) {
                case 'range':
                    $query['query']['bool']['must'][] = array('range'=>array($v['field'] => array($v['operation'] => $v['value'])));
                    break;
                case 'match':
                    $query['query']['bool']['must'][] = array('match' => array($k => $v['value']));
                    break;
                case 'wildcard':
                    if (!isset($v['bool'])) {
                        $query['query']['bool']['must'][] = array('wildcard' => array($k => "*{$v['value']}*"));
                    } else {
                        $query['query']['bool']['must'][] = array('bool' => array($v['operation'] => array(array('wildcard' => array($k => "*{$v['value']}*")), array('match' => array($v['field'] => array('query'=>$v['value'], 'operator'=>'and'))))));
                    }
                    break;
                case 'terms':
                    $query['query']['bool']['must'][] = array('terms' => array($k => $v['value']));
                    break;
                case 'should':
                    $values = explode(',', $v['value']);
                    foreach ($values as $val) {
                        $query['query']['bool']['should'][] = array('match' => array($k => $val));
                    }
                    $query['query']['bool']['minimum_should_match'] = 1;//至少匹配一个
                    break;
            }
        }

        $params = array(
            "size" => $rows,
            "from" => $from,
            'index'=>$index,
            'type'=>$esType,
            'body'=>$query,
        );

        if (!empty($export)) {
            $params['size'] = 10000;
            $params['search_type'] = 'scan';
            $params['scroll'] = '30s';
        }

        $sources = array();
        $total = 10;

        try {
            $hosts = $this->container->getParameter('elasticsearch_hosts');
            $client = new Client(array('hosts'=>$hosts));
            $result = $client->search($params);
            if (!empty($export)) {
                $scrollId = $result['_scroll_id'];
                while (true) {
                    $response = $client->scroll(
                        array(
                            "scroll_id" => $scrollId,
                            "scroll" => "30s",
                        )
                    );

                    if (count($response['hits']['hits']) > 0) {
                        $sources = array_merge($sources, $response['hits']['hits']);
                        $total = $response['hits']['total'];
                        $scrollId = $response['_scroll_id'];
                    } else {
                        break;
                    }
                }
            } else {
                $sources = $result['hits']['hits'];
                $total = $result['hits']['total'];
            }
        } catch (\Exception $e) {
            $logger = $this->container->get('logger');
            $logger->error($e->getMessage());

            return array(
                'code' => 500,
                'message' => '发生异常',
                'total' => 0,
                'page' => 0,
                'total_pages' => 0,
                'data' => array(),
            );
        }

        $data = array();
        foreach ($sources as $source) {
            $data[] = $source['_source'];
        }

        $data = $this->processData($data, $condition['term']['vcc_id']['value'], $repType, $type);

        return array(
            'code' => 200,
            'message' => 'ok',
            'records' => $total,
            'page' => $page,
            'total' => ceil($total/$rows),
            'rows' => $data['data'],
            'footer' => $data['footer'],
        );
    }

    /**
     * 处理ES搜索条件
     * @param $condition
     * @param $type
     * @param $dataIds
     * @return mixed
     */
    private function setCondition($condition, $type, $dataIds)
    {
        $data = array('type'=>'should');
        $data['value'] = $dataIds;
        switch ($type) {
            case 'queue':
                $condition['term']['que_id'] = $data;
                break;
            case 'agent':
                $condition['term']['ag_id'] = $data;
                break;
            case 'group':
                $condition['term']['group_id'] = $data;
                break;
        }

        return $condition;
    }

    /**
     * 给每条数据处理相关字段；
     * @param $value
     * @param $type
     * @return mixed
     */
    private function proccessValue($value, $type)
    {
        $conn =  $this->container->get('doctrine.dbal.default_connection');
        switch ($type) {
            case 'system':
                break;
            case 'queue':
                if (isset($value['que_id'])) {
                    $queName= $conn->fetchColumn(
                        "SELECT que_name FROM win_queue WHERE id = ?",
                        array($value['que_id'])
                    );
                    $value['queue_name'] = empty($queName) ? '' : $queName;
                }
                break;
            case 'agent':
                if (isset($value['ag_id'])) {
                    $data = $conn->fetchAssoc(
                        "SELECT ag_name,ag_num FROM win_agent WHERE id = ?",
                        array($value['ag_id'])
                    );

                    $value['agent'] = empty($data) ? '' : $data['ag_num'].' '.$data['ag_name'];
                }
                break;
            case 'group':
                if (isset($value['group_id'])) {
                    $groupName = $conn->fetchColumn(
                        "SELECT group_name FROM win_group WHERE group_id = ?",
                        array($value['group_id'])
                    );
                    $value['group_name'] = empty($groupName) ? '' : $groupName;
                }
                break;
        }

        return $value;
    }
}
